<?php
header("Content-Type: text/html; charset=iso-8859-1");

function game_save($mysqli,$gameID,$role,$players,$rndGame) {
    if ($players != null) {
        $selected = true;
    } else {
        $selected = false;
    }
	print("<div id='gameSaveContainer'>");
    print("<form id='playersForm'>");
	print("<div id='gameSave' class='saveGame scrollbarover'>");
    TS("gridtable");
    TH("");
    //TH("Spieler");
	print("<th colspan=2>Spieler</th>");
	if($role == 'admin') {
		 TH("Anz.");
	}
    TH("Abs.");
    TH("Pkt.");

    $checkCount = 0;
    if ($selected) {
        foreach ($players as $value) {
            $checkCount +=1;
        }
    }
	$pos=1;


	getPlayersByGameCount($mysqli,1,$selected,$role,$pos,$players,$checkCount);
	getPlayersByGameCount($mysqli,2,$selected,$role,$pos,$players,$checkCount);
	
    TE();
	print("</div>");
	
	TS("gridtable");
	TRS();
	print("<td>");

    $res = $mysqli->query("select distinct g.id, g.game_name, count(gd.id) count from game_data gd, games g where gd.game_id = g.id group by g.id, g.game_name, gd.game_id order by count desc");
    print("<select name='game_id' size='1' id='gameSelect'>");
    print("<option value=''></option>");
	$i=1;

    while (($row = $res->fetch_assoc()) != false) {
		if ($row['id']==$gameID || $i==$rndGame) {
			print("<option selected value=".$row['id'].">".$row['game_name']." (".$row['count'].")</option>");
		} else {
			print("<option value=".$row['id'].">".$row['game_name']." (".$row['count'].")</option>");
		 }
		 $i+=1;
        //print("<option value=" . $row['id'] . ">" . $row['game_name'] . "</option>");
    }
	$res->close();
    print("</select>");
    TDE();
	if($role == 'admin') {
		print("<td colspan='4'>");
	} else {
		print("<td colspan='3'>");
	}
	print("<input type='hidden' name='role' value='".$role."'>");
	print("<input type='submit' name='startgame' id='startgame' value='Start' disabled />");
	TDE();
    TRE();
	TRS();
	print("<td colspan='3'>");
	print("<div id='gameselecttree' style='display:none;'>");
				getGamesBySql($mysqli,"select distinct g.id, g.game_name, g.idNew, count(gd.id) count from game_data gd, games g where gd.game_id = g.id group by g.id, g.game_name, gd.game_id order by count desc limit 10","Top Spiele");
				print("<ul>");
				print("  <li>X01");
				getGamesBySql($mysqli,"select distinct g.id, g.game_name, g.idNew, count(gd.id) count from game_data gd, games g, games_v2 g2 where gd.game_id = g.id and g.idNew = g2.id and g2.gameGroup = 'X01' and game_name like '101%' group by g.id, g.game_name, gd.game_id order by game_name","101");
				getGamesBySql($mysqli,"select distinct g.id, g.game_name, g.idNew, count(gd.id) count from game_data gd, games g, games_v2 g2 where gd.game_id = g.id and g.idNew = g2.id and g2.gameGroup = 'X01' and game_name like '301%' group by g.id, g.game_name, gd.game_id order by game_name","301");
				getGamesBySql($mysqli,"select distinct g.id, g.game_name, g.idNew, count(gd.id) count from game_data gd, games g, games_v2 g2 where gd.game_id = g.id and g.idNew = g2.id and g2.gameGroup = 'X01' and game_name like '501%' group by g.id, g.game_name, gd.game_id order by game_name","501");
				print("  </li>");
				print("</ul>");
				getGamesBySql($mysqli,"select distinct g.id, g.game_name, g.idNew, count(gd.id) count from game_data gd, games g, games_v2 g2 where gd.game_id = g.id and g.idNew = g2.id and g2.gameGroup = 'Half It' group by g.id, g.game_name, gd.game_id order by game_name","Half It");
				getGamesBySql($mysqli,"select distinct g.id, g.game_name, g.idNew, count(gd.id) count from game_data gd, games g, games_v2 g2 where gd.game_id = g.id and g.idNew = g2.id and g2.gameGroup = 'Count Up' group by g.id, g.game_name, gd.game_id order by game_name","Count Up");
				getGamesBySql($mysqli,"select distinct g.id, g.game_name, g.idNew, count(gd.id) count from game_data gd, games g, games_v2 g2 where gd.game_id = g.id and g.idNew = g2.id and g2.gameGroup = 'Scream' group by g.id, g.game_name, gd.game_id order by game_name","Scream");
	print("</div>");
	TDE();
	print("<td style='vertical-align: top'>");
    print("<input type='submit' name='saveGame' value='Speichern'/>");
	TDE();
	TRE();
	TRS();
	print("<td colspan='4'>");
	//print("<div id='livelink'></div>");
	TDE();
	TE();
    BR();
	print("</div>");
	print("</form>");
	print("</div>");
}

function getGamesBySql($mysqli,$sql,$FolderName) {
	$res = $mysqli->query($sql);
	print("<ul>");
	print("		<li>".$FolderName);
				while (($row = $res->fetch_assoc()) != false) {
	print("		  <ul>");
	print("			<li data-game-id='".$row['id']."' data-game-id-new='".$row['idNew']."'>".$row['game_name']." (".$row['count'].")</li>");
	print("		  </ul>");
				}
	print("		</li>");
	print("</ul>");
}

function getPlayersByGameCount($mysqli,$type,$selected,$role,$pos,$players,$checkCount) {
	$limit = 20;
	$res = $mysqli->query("select * from players, (select sum(score_abs)/count(*) score_abs,player_id from view_score_kw where score!=-1 group by player_id ) score where players.id = score.player_id order by score_abs desc");
    while (($row = $res->fetch_assoc()) != false) {
        $resCount = $mysqli->query("select count(*) count from game_data where player_id =" . $row['id'] . " and score!=-1");
        $rowCount = $resCount->fetch_assoc();
        $count = $rowCount['count'];

		if (($type == 1 && $count > $limit && $row["email"] != 'disabled') || ($type == 2 && $count <= $limit)) {
			$resScoreAbs = $mysqli->query("select sum(score_abs)/count(*) score_abs from view_score_kw where player_id =" . $row['id'] . " and score!=-1");
			$rowScoreAbs = $resScoreAbs->fetch_assoc();
			$scoreAbs = round($rowScoreAbs['score_abs'], 3);
			TRS();
			$checked = false;
			if ($selected) {
				foreach ($players as $value) {
					if ($row['id'] == $value) {
						$checked = true;
					}
				}
			}
			$wins = getWinCount($mysqli,null, $row['id'] );
			$lost = getLostCount($mysqli,null, $row['id'] );
			$gameCount = getGameCount($mysqli,null, $row['id'] );
			$winQuote = round($wins / $gameCount * 100,2);
			$lostQuote = round($lost / $gameCount * 100,2);

			if($role == 'admin') {
				$titleStatistic = "data-title='Siege: ".$wins." (".$winQuote."%)<br />Niederlagen: ".$lost." (".$lostQuote."%)&#13;Anzahl Spiele: ".$gameCount."'";
			} else {
				$titleStatistic = "data-title='Siege: ".$wins." (".$winQuote."%)<br />Niederlagen: ".$lost." (".$lostQuote."%)'";
			}
			
			$titleStatistic .= " data-player-id='".$row['id']."'";

			if ($checked) {
				$class = "statisticByPlayer";
				TDS("color_" . $row["id"]);
				print("<input type='checkbox' name='playerid[]' value='" . $row['id'] . "' checked='checked' />");
				TDE();
				TDS("minsize");
				if ($row["email"] == 'disabled') {
					TDE();
					print("<td class='".$class."'".$titleStatistic.">");
					print("<span style='color:lightgrey'>".$row['firstName'] . " " . $row['lastName']."</span>");
				} else if ($count > $limit) {
					print("<span style='font-weight:bold'>".$pos."."."</span>");
					TDE();
					print("<td class='".$class."'".$titleStatistic.">");
					print("<span style='font-weight:bold'>".$row['firstName'] . " " . $row['lastName']."</span>");
					$pos+=1;
				} else {
					TDE();
					print("<td class='".$class."'".$titleStatistic.">");
					print($row['firstName'] . " " . $row['lastName']." (".strval(intval($count)-10) .")");
				}
				TDE();
				if($role == 'admin') {
					TD();
					print($count);
					TDE();
				}
				TD();
				print($scoreAbs);
				TDE();
			} else {
				$class = "disabled statisticByPlayer";
				TDS("color_" . $row["id"]);
				print("<input type='checkbox' name='playerid[]' value='" . $row['id'] . "' />");
				TDE();
				TDS("disabled");
				if ($row["email"] == 'disabled') {
					TDE();
					print("<td class='".$class."'".$titleStatistic.">");
					print("<span style='color:lightgrey'>".$row['firstName'] . " " . $row['lastName']."</span>");
				} else if ($count > $limit) {
					print($pos.".");
					TDE();
					print("<td class='".$class."'".$titleStatistic.">");
					print("<span style='font-weight:bold'>".$row['firstName'] . " " . $row['lastName']."</span>");
					$pos+=1;
				} else {
					TDE();
					print("<td class='".$class."'".$titleStatistic.">");
					print($row['firstName'] . " " . $row['lastName']." (".strval(intval($count)-$limit) .")");
				}
				if($role == 'admin') {
					TDS("disabled");
					print($count);
					TDE();
				}
				TDS("disabled");
				print($scoreAbs);
				TDE();
			}
			if ($checked) {
				print("<td class='selectPoints'><select name='player_" . $row['id'] . "' size='1'>");
				for ($i = 0; $i < $checkCount; $i++) {
					print("<option value='" . $i . "'>" . $i . "</option>");
				}
				print("</select></td>");
			} else {
				print("<td class='disabled'></td>");
			}
			TRE();
		}
	}
	$res->close();
}

function games_table($mysqli, $yearkw, $playerID, $gameID) {
    $dataFound = false;
	$sql = null;
	if ($playerID != null && $gameID != null) {
		$sql = "select distinct gd.date,g.game_name,gd.id,gd.game_uuid, YEAR(gd.date) year, WEEK(gd.date,1) kw, g.id gameID from game_data gd, games g where gd.game_id = " . $gameID . " and gd.player_id = " . $playerID . " and gd.game_id = g.id order by gd.id desc";
		$statisticView = true;
	} else if ($playerID != null) {
		$sql = "select distinct gd.date,g.game_name,gd.id,gd.game_uuid, YEAR(gd.date) year, WEEK(gd.date,1) kw, g.id gameID from game_data gd, games g where gd.player_id = " . $playerID . " and gd.game_id = g.id order by gd.id desc";
		$statisticView = true;
	} else if ($gameID != null) {
		$sql = "select distinct gd.date,g.game_name,gd.id,gd.game_uuid, YEAR(gd.date) year, WEEK(gd.date,1) kw, g.id gameID from game_data gd, games g where gd.game_id = " . $gameID . " and gd.game_id = g.id order by gd.id desc";
		$statisticView = false;
	} else{
		$sql = "select distinct gd.date,g.game_name,gd.id,gd.game_uuid, YEAR(gd.date) year, WEEK(gd.date,1) kw, g.id gameID from game_data gd, games g where CONCAT(YEAR(gd.date),WEEK(gd.date,1)) = '" . $yearkw . "' and gd.game_id = g.id order by gd.id desc";
		$statisticView = false;
	}
	$res = $mysqli->query($sql);
    while (($row = $res->fetch_assoc()) != false) {
        $dataFound = true;
    }

	print("<div id='gamesTable'>");

    if ($dataFound) {
        TS("gridtable");
        TH("Datum");
        TH("Spiel");
		TH("Platz 1");
		TH("Platz 2");
		TH("Platz 3");
		TH("Platz 4");
		TH("Platz 5");
		TH("Platz 6");
		TH("Platz 7");
		TH("Platz 8");
        //print("<th colspan='10'></th>");
        $res = $mysqli->query($sql);
        while (($row = $res->fetch_assoc()) != false) {
			$week = date('W', strtotime($row['date']));
			if (isSet($memWeek) && $memWeek != $week) {
				TRS();TD();echo "KW ".$week;TDE();TRE();
			}
            TRS();
            print("<td nowrap class='disabled deleteGame' data-id='".$row["id"]."' data-game-uuid='".$row["game_uuid"]."'>");
            print($row['date']);
            TDE();
            print("<td nowrap class='disabled statisticByGame' data-game-id='".$row["gameID"]."' data-game-uuid='".$row["game_uuid"]."'>");
            print($row['game_name']);
            TDE();
            $res2 = $mysqli->query("select * from game_data, players where game_data.player_id = players.id and game_data.id =" . $row["id"] . " order by score desc");
            while (($row2 = $res2->fetch_assoc()) != false) {
				if($statisticView == true) {
					if ($playerID == $row2["player_id"]) {
						$class="color_".$row2["player_id"]." statisticByGameAndPlayer";
					} else {
						$class="statisticByGameAndPlayer";
					}
				} else {
					$class="color_".$row2["player_id"]." statisticByGameAndPlayer";
				}
                print("<td nowrap class='".$class."' data-player-id='".$row2["player_id"]."' data-game-id='".$row["gameID"]."' data-game-uuid='".$row["game_uuid"]."'>");
				print($row2["firstName"] . " [" . $row2["score"] . "]");
				if($row["game_uuid"] != 0) {
					$maxscore = getAVGByGameUUID($mysqli,$row["game_uuid"],$row2["player_id"]);
					$topscore = getTopScoreByGameUUID($mysqli,$row["game_uuid"],$row2["player_id"]);
					if ($maxscore > 60) {
						print("<span class='red blink'> AVG[".$maxscore."]</span>");
					} 
					if ($topscore > 100) {
						print("<span class='red blink'> TOP[".$topscore."]</span>");
					} 
				} 
                TDE();
            }
			$memWeek = $week;
            TRE();
        }
		$res->close();
        TE();
    }
	print("</div>");
}

function getRandomGame($mysqli,$role,$gameID) {

	$i = 1;
	print("<div id='randomGameTabel' class='randomGameTabel scrollbarover'>");
	TS("gridtable");
	if ($gameID == null) {
		$res = $mysqli->query("select distinct g.id, g.game_name, count(gd.id) count from game_data gd, games g where gd.game_id = g.id group by g.id, g.game_name, gd.game_id ORDER BY RAND() LIMIT 1");
	} else{
		$res = $mysqli->query("select id,game_name from games where id = ".$gameID);
	}
    while (($row = $res->fetch_assoc()) != false) {
		$game_id = $row['id'];
		print("<th colspan='2'>N&auml;chstes Spiel: ".$row['game_name']."</th>");
		TH("Pkt.");
    }
	$res->close();
	if($role == 'admin') {
		TH("Anz.");
	}
	TH("");

	$res = $mysqli->query("select players.id playerID,players.firstName,players.lastName,games.id,games.game_name,round(sum(game_data.score/maxscore.maxgamescore)/count(*),2) score, count(*) count from
game_data, games, players,
(select id,game_id,max(score) maxgamescore from game_data group by id) maxscore
where maxscore.id = game_data.id
and game_data.game_id = games.id
and game_data.player_id = players.id
and games.id = ".$game_id."
group by players.id,players.firstName,players.lastName,games.game_name
order by score desc");
	$i = 1;
	while (($row = $res->fetch_assoc()) != false) {
		$resScoreAbs = $mysqli->query("select sum(score_abs)/count(*) score_abs from view_score_kw where player_id =" . $row['playerID'] . " and score!=-1");
        $rowScoreAbs = $resScoreAbs->fetch_assoc();
        $scoreAbsAll = round($rowScoreAbs['score_abs'], 3);
		if ($scoreAbsAll>$row['score']) {
			$trend = "down";
		} else {
			$trend = "up";
		}
		TRS();
		TDS("minsize");
			print($i.".");
		TDE();
		print("<td class='statisticByGameAndPlayer' data-player-id='".$row['playerID']."' data-game-id='".$game_id."'>");
			print($row['firstName']." ".$row['lastName']);
		TDE();
		TD();
			print($row['score']);
		TDE();
		TDS($trend);
		TDE();
		if($role == 'admin') {
			TD();
				print($row['count']);
			TDE();
		}
		TRE();
		$i+=1;
		if($i>10) break;
    }
    $res->close();
    TE();
	print("</div>");
	BR();
}

function getTopScores($mysqli,$role) {

	$i = 1;
	print("<div id='topScoresTabel' class='topScoresTabel scrollbarover'>");
	TS("gridtable");
	print("<th colspan='4'>Top Scores</th>");

	$res = $mysqli->query("select maxscore.creation_date,games.game_name,games.id,maxscore.game_uuid,players.firstName,players.lastName,maxscore.player_id,maxscore.maxscore maxscore
							from (select game_uuid,game_id,player_id,sum(score*score_multiplier) maxscore,creation_date from game_score_v2 group by player_id, game_uuid, round) maxscore,
							players,
							games
							where players.id = maxscore.player_id
							and games.idNew = maxscore.game_id
							order by maxscore desc, week(maxscore.creation_date) desc limit 50;");
	while (($row = $res->fetch_assoc()) != false) {
		TRS();
		$class="color_".$row["player_id"];
		print("<td class='date'>");
			print(date_format(new DateTime($row["creation_date"]),'d.m.Y H:i'));
		TDE();
		print("<td nowrap class='statisticByGame' data-game-id='".$row["id"]."' data-game-uuid='".$row["game_uuid"]."'>");
			print($row["game_name"]);
		TDE();
		print("<td nowrap class='".$class." statisticByGameAndPlayer' data-player-id='".$row["player_id"]."' data-game-id='".$row["id"]."' data-game-uuid='".$row["game_uuid"]."'>");
			print($row["firstName"]);
		TDE();
		TD();
			print($row["maxscore"]);
		TDE();
		TRE();
    }
    $res->close();
    TE();
	print("</div>");
}

function getTopScore() {
	global $mysqli;
	$sql = "select maxscore.creation_date,games.game_name,games.id,maxscore.game_uuid,players.firstName,players.lastName,maxscore.player_id,maxscore.maxscore maxscore
							from (select game_uuid,game_id,player_id,sum(score*score_multiplier) maxscore,creation_date from game_score_v2 group by player_id, game_uuid, round) maxscore,
							players,
							games
							where players.id = maxscore.player_id
							and games.idNew = maxscore.game_id
							order by maxscore desc, week(maxscore.creation_date) desc limit 1;";
	$topScoreDate = new DateTime($mysqli->query($sql)->fetch_object()->creation_date);
	$text = date_format($topScoreDate,'d.m.Y').", ".$mysqli->query($sql)->fetch_object()->firstName.", ".$mysqli->query($sql)->fetch_object()->game_name.", ".$mysqli->query($sql)->fetch_object()->maxscore;
	
	return $text;
}


function getTopAvgs($mysqli,$role) {

	$i = 1;
	print("<div id='topAgvTabel' class='topScoresTabel scrollbarover'>");
	TS("gridtable");
	print("<th colspan='4'>Top 3-Dart-Average</th>");

	$res = $mysqli->query("select maxscore.creation_date,games.game_name,games.id,maxscore.count,maxscore.game_id,maxscore.game_uuid,players.firstName,players.lastName,maxscore.player_id,round(maxscore.maxscore,0) maxscore
							from (select game_uuid,game_id,player_id,(sum(score*score_multiplier)/count(*))*3 maxscore,creation_date,count(*) count from game_score_v2 group by player_id, game_uuid) maxscore,
							players,
							games,
							games_v2
							where players.id = maxscore.player_id
							and games.idNew = maxscore.game_id
							and games.idNew = games_v2.id
							and games_v2.gameGroup = 'X01'
							order by maxscore desc, week(maxscore.creation_date) desc limit 50");
	while (($row = $res->fetch_assoc()) != false) {
		TRS();
		$class="color_".$row["player_id"];
		print("<td class='date'>");
			print(date_format(new DateTime($row["creation_date"]),'d.m.Y H:i'));
		TDE();
		print("<td nowrap class='statisticByGame' data-game-id='".$row["id"]."' data-game-uuid='".$row["game_uuid"]."'>");
			print($row["game_name"]);
		TDE();
		print("<td nowrap class='".$class." statisticByGameAndPlayer' data-player-id='".$row["player_id"]."' data-game-id='".$row["id"]."' data-game-uuid='".$row["game_uuid"]."'>");
			print($row["firstName"]);
		TDE();
		TD();
			print($row["maxscore"]);
		TDE();
		TRE();
    }
    $res->close();
    TE();
	print("</div>");
}

function getTopAvg() {
	global $mysqli;
	$sql = "select maxscore.creation_date,games.game_name,games.id,maxscore.count,maxscore.game_id,maxscore.game_uuid,players.firstName,players.lastName,maxscore.player_id,round(maxscore.maxscore,0) maxscore
							from (select game_uuid,game_id,player_id,(sum(score*score_multiplier)/count(*))*3 maxscore,creation_date,count(*) count from game_score_v2 group by player_id, game_uuid) maxscore,
							players,
							games,
							games_v2
							where players.id = maxscore.player_id
							and games.idNew = maxscore.game_id
							and games.idNew = games_v2.id
							and games_v2.gameGroup = 'X01'
							order by maxscore desc, week(maxscore.creation_date) desc limit 1;";
	$topScoreDate = new DateTime($mysqli->query($sql)->fetch_object()->creation_date);
	$text = date_format($topScoreDate,'d.m.Y').", ".$mysqli->query($sql)->fetch_object()->firstName.", ".$mysqli->query($sql)->fetch_object()->game_name.", ".$mysqli->query($sql)->fetch_object()->maxscore;
	
	return $text;
}

function getAVGByGameUUID($mysqli,$gameUUID,$playerID) {
	$sql = "select (sum(score*score_multiplier)/count(*))*3 maxscore,creation_date,count(*) count from game_score_v2 where game_uuid = ".$gameUUID." and player_id = ".$playerID;
	$res = $mysqli->query($sql);
	if (($row = $res->fetch_assoc()) != false) {
		$maxScore = $row['maxscore'];
	}
	return round($maxScore,0);
}

function getTopScoreByGameUUID($mysqli,$gameUUID,$playerID) {
	$sql = "select sum(score*score_multiplier) maxscore,creation_date,count(*) count from game_score_v2 where game_uuid = ".$gameUUID." and player_id = ".$playerID." group by round order by maxscore desc";
	$maxScore = 0;
	$res = $mysqli->query($sql);
	if (($row = $res->fetch_assoc()) != false) {
		$maxScore = $row['maxscore'];
	}
	return round($maxScore,0);
}

function games_kw_summary($mysqli, $yearkw, $yearkwselected) {
    $minPlayedGamesPercentage = 0.20;

    $res = $mysqli->query("select max(count_games) as maxCount from view_score_kw_winner where yearkw=" . $yearkw);
    if (($row = $res->fetch_assoc()) != false) {
        $maxCount = $row['maxCount'];
    }
	$res->close();
    $minPlayedGames = $minPlayedGamesPercentage * $maxCount;

    $res = $mysqli->query("select * from view_all_kw");
	print("<div id='gamesKWSummary' class='gamesKWSummary scrollbarover'>");
    TS("gridtable");
    print("<th colspan=2>");
    print("<form action='" . $_SERVER['PHP_SELF'] . "'>");
    print("<select onchange='this.form.submit()' name='kw' size='1'>");

    while (($row = $res->fetch_assoc()) != false) {
        if ($row['kw2'] == $yearkwselected) {
            print("<option selected value=" . $row['kw2'] . ">" . $row['kw'] . "</option>");
        } else {
            print("<option value=" . $row['kw2'] . ">" . $row['kw'] . "</option>");
        }
    }
	$res->close();
    print("</select>");
    print("</form>");
    print("</th>");
    //TH("Punkte (".round($minPlayedGames,0)."/".$maxCount.")");
	TH("S");
	TH("N");
	TH("Pkt.");
	TH("");
    $count = 1;
    $res = $mysqli->query("select * from view_score_kw_winner where yearkw=" . $yearkw);
    while (($row = $res->fetch_assoc()) != false) {
		$class = "statisticByPlayer ";
        TRS("gridtable");
        if ($row['count_games'] >= $minPlayedGames) {
            if ($count == 1) {
                $class .= "gold";
            } else if ($count == 2) {
                $class .= "silver";
            } else if ($count == 3) {
                $class .= "bronze";
            } else {
                $class .= "";
            }
        } else {
            $class .= "";
        }

		$wins = getWinCount($mysqli,$yearkw, $row['player_id'] );
		$lost = getLostCount($mysqli,$yearkw, $row['player_id'] );
		$gameCount = getGameCount($mysqli,$yearkw, $row['player_id'] );
		$winQuote = round($wins / $gameCount * 100,2);
		$lostQuote = round($lost / $gameCount * 100,2);
		
		$resScoreAbs = $mysqli->query("select sum(score_abs)/count(*) score_abs from view_score_kw where player_id =" . $row['player_id'] . " and score!=-1");
        $rowScoreAbs = $resScoreAbs->fetch_assoc();
        $scoreAbsAll = round($rowScoreAbs['score_abs'], 3);
		$trendeDiff = round($row['score_abs']-$scoreAbsAll,2);
		if ($scoreAbsAll>$row['score_abs']) {
			$trend = "down";
		} else {
			$trend = "up";
		}
		
		
		$titleStatistic = "data-title='Siege: ".$wins." (".$winQuote."%)<br />Niederlagen: ".$lost." (".$lostQuote."%)<br />Anzahl Spiele: ".$gameCount."<br/>Trend: ".$trendeDiff."'";
        TDS($class.' minsize');
		if ($row['count_games'] >= $minPlayedGames) {
			if($row['contraction'] != null) {
				print($count .".");
				TDE();
				print("<td data-player-id='".$row['player_id']."' class='".$class."' ".$titleStatistic.">");
				print("<span style='font-weight:bold'>".$row['contraction']."</span>");
				TDE();
				TDS($class);
				print($wins);
				TDE();
				TDS($class);
				print($lost);
			} else {
				print($count .".");
				TDE();
				print("<td data-player-id='".$row['player_id']."' class='".$class."' ".$titleStatistic.">");
				print("<span style='font-weight:bold'>".$row['firstName'] . " " . $row['lastName']."</span>");
				TDE();
				TDS($class);
				print($wins);
				TDE();
				TDS($class);
				print($lost);
			}
        } else {
            print("<div class=\"notConsider\">");
			if($row['contraction'] != null) {
				TDE();
				print("<td data-player-id='".$row['player_id']."' class='".$class."' ".$titleStatistic.">");
				print($row['contraction'] . " (-". strval(intval($minPlayedGames)-intval($row['count_games'])+1) . ")");
				TDE();
				TDS($class);
				print($wins);
				TDE();
				TDS($class);
				print($lost);
			} else {
				TDE();
				print("<td data-player-id='".$row['player_id']."' class='".$class."' ".$titleStatistic.">");
				print($row['firstName'] . " " . $row['lastName'] . " (-" . strval(intval($minPlayedGames)-intval($row['count_games'])+1) . ")");
				TDE();
				TDS($class);
				print($wins);
				TDE();
				TDS($class);
				print($lost);
			}
			print("</div>");
        }

        TDE();
        TDS($class);
		if ($row['count_games'] >= $minPlayedGames) {
			print(round($row['score_abs'], 3));
			TDE();
			TDS($class." ".$trend);
		} else {
			print("<div class=\"notConsiderScore\">" . round($row['score_abs'], 3))."</div>";
			TDE();
			TDS($class." ".$trend);
		}
        TDE();
        /*TDS($class);
        print($row['count_games']);
        TDE();*/
        TRE();
        if ($row['count_games'] >= $minPlayedGames) {
            $count+=1;
        }
    }
	$res->close();
    TE();
	print("</div>");
    BR();
}

function getWinCount($mysqli,$yearkw, $player_id ){
	if($yearkw == null) {
		$sql = "select count(*) count from view_games_data a where score = (select max(score) maxscore from view_games_data where a.id=id) and player_id = ".$player_id;
	} else {
		$sql = "select count(*) count from view_games_data a where CONCAT(YEAR(date),WEEK(date,1)) = '".$yearkw."' and score = (select max(score) maxscore from view_games_data where CONCAT(YEAR(date),WEEK(date,1)) = '".$yearkw."' and a.id=id) and player_id = ".$player_id;
	}
	$res2 = $mysqli->query($sql);
	$row2 = $res2->fetch_assoc();
	$res2->close();
	return $row2['count'];
}
function getLostCount($mysqli,$yearkw, $player_id ){
	if($yearkw == null) {
		$sql = "select count(*) count from view_games_data a where score = 0 and player_id = ".$player_id;
	} else {
		$sql = "select count(*) count from view_games_data a where CONCAT(YEAR(date),WEEK(date,1)) = '".$yearkw."' and score = 0 and player_id = ".$player_id;
	}
	$res2 = $mysqli->query($sql);
	$row2 = $res2->fetch_assoc();
	$res2->close();
	return $row2['count'];
}
function getGameCount($mysqli,$yearkw, $player_id ){
	if($yearkw == null) {
		$sql = "select count(*) count from view_games_data a where player_id = ".$player_id ;
	} else {
		$sql = "select count(*) count from view_games_data a where CONCAT(YEAR(date),WEEK(date,1)) = '".$yearkw."' and player_id = ".$player_id ;
	}
	$res2 = $mysqli->query($sql);
	$row2 = $res2->fetch_assoc();
	$res2->close();
	return $row2['count'];
}

function BR() {
    print("<br/>");
}

function TS($class) {
    if ($class != null) {
        print("<table class='" . $class . "'>");
    } else {
        print("<table>");
    }
}

function TE() {
    print("</table>");
}

function TH($value) {
    print("<th nowrap>" . $value . "</th>");
}

function THS() {
    print("<th nowrap>");
}

function THE() {
    print("</th>");
}

function TD() {
    print("<td nowrap>");
}

function TDS($class) {
    print("<td nowrap class='" . $class . "'>");
}

function TDE() {
    print("</td>");
}

function TRS() {
    print("<tr>");
}

function TRE() {
    print("</tr>");
}

function startsWith($haystack, $needle) {
    return $needle === "" || strpos($haystack, $needle) === 0;
}

function endsWith($haystack, $needle) {
    return $needle === "" || substr($haystack, -strlen($needle)) === $needle;
}

?>